#!/usr/bin/python3

import pymysql


def query(sql):
    # print(sql)
    db = pymysql.connect("localhost", "sysUser", "sysUser", "monitor")
    cursor = db.cursor()
    cursor.execute(sql)
    data = cursor.fetchone()
    db.close()
    return data


def querys(sql):
    # print(sql)
    db = pymysql.connect("localhost", "sysUser", "sysUser", "monitor")
    cursor = db.cursor()
    cursor.execute(sql)
    data = cursor.fetchall()
    db.close()
    return data


def update(sql):
    print(sql)
    db = pymysql.connect("localhost", "sysUser", "sysUser", "monitor")
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()
    data = cursor.fetchall()
    db.close()
    return data

#
# def searchUser(username, password):
#     sql = "select id,name,pass,email,state from user where name=\"%s\" and pass=\"%s\"" % (username, password)
#     return query(sql)  # id,name,pass
#
#
# def allUser():
#     sql = "SELECT user.id,user.name,user.pass,email,user.state FROM user ORDER BY id"
#     return querys(sql)
#
#
# def deviceUser(d_id):
#     sql = "select user.id,user.name,user.pass,email,user.state from user, user_device where user_device.u_id = user.id and user_device.d_id = %s" % d_id
#     return querys(sql)


def updateDeviceUserState(d_id, u_id, state):
    if state == "active":
        sql = "insert into user_device(u_id, d_id) value(%s, %s)" % (u_id, d_id)
        return update(sql)
    else:
        sql = "delete from user_device where u_id=\"%s\" and d_id=\"%s\"" % (u_id, d_id)
        return update(sql)

#
# def device_data(user, device_id):
#     sql = "select * from device_view where u_name=\"%s\" and d_id=\"%s\"" % (user, device_id)
#     return querys(sql)

#
# def device_detail(d_id):
#     sql = "select id,name,type, address,state,others from device where id=%s" % d_id
#     return query(sql)


# def devicesByPage(user_id, page_id, page_rows):
#     sql = "select device.id,device.name,address,device.state,serial,time from device,user_device where user_device.d_id=device.id and user_device.u_id=%s limit %s, %s  " % (user_id, str(page_rows * (page_id - 1)), str(page_rows))
#     return querys(sql)


# def allSensorByDid(d_id):
#     sql = "select id, name,value,min,max,unit,state from sensor where d_id= %s " % d_id
#     return querys(sql)


# def sensorByDid(d_id):
#     sql = "select id, name,value,min,max,unit from sensor where state=\"active\" and d_id= %s " % d_id
#     return querys(sql)


# def historyByDid(d_id, page_id, page_rows):
#     sql = "SELECT history.id,name,history.value,unit,time,min,max FROM history,sensor where state=\"active\" and history.s_id = sensor.id and d_id = %s order by history.s_id limit %s, %s" % (d_id, str(page_rows * (page_id - 1)), str(page_rows))
#     # print(sql)
#     return querys(sql)
#
#
# def history_insert(new_data):
#     sql = "insert into history (value) value (\"%s\")" % new_data
#     return insert(sql)


def getUserByNamePassword(username, password):
    sql = "select id,name,pass,email,state from user where user.state=\"active\" and name=\"%s\" and pass=\"%s\"" % (username, password)
    return query(sql)


def getUsers():
    sql = "SELECT id,name,pass,email,state FROM user  where user.state=\"active\" ORDER BY id "
    return querys(sql)


def getUserByID(u_id):
    sql = "select id,name,pass,email,state from user where user.state=\"active\" and id=%s order by id" % u_id
    return query(sql)


def updateUserByObj(obj):
    if getUserByID(obj.id):
        sql = "update user set name=\"%s\",pass=\"%s\",email=\"%s\",state=\"%s\"  where id=%s " % (obj.name, obj.password, obj.email, obj.state, obj.id)
        return update(sql)
    else:
        sql = "insert into user(name,pass,email,state) value (\"%s\",\"%s\",\"%s\",\"%s\")" % (obj.name, obj.password, obj.email, obj.state)
        return update(sql)


def getUsersByDeviceID(d_id):
    sql = "select user.id,user.name,user.pass,email,user.state from user, user_device where user.state=\"active\" and user_device.u_id = user.id and user_device.d_id = %s order by user.id" % d_id
    return querys(sql)


def getSensorByID(s_id):
    sql = "select id, d_id, name,value,last,unit,max,min,description,state from sensor where id = %s order by id" % s_id
    return query(sql)


def updateSensorByObj(obj):
    sql = "update sensor set d_id=\"%s\",name=\"%s\",unit=\"%s\",max=\"%s\",min=\"%s\",description=\"%s\",state=\"%s\" where id=%s " % (obj.d_id, obj.name, obj.unit, obj.max, obj.min, obj.description, obj.state, obj.id)
    return update(sql)


def getActiveSensorsByDeviceID(d_id):
    sql = "select id, d_id, name,value,last,unit,max,min,description,state from sensor where state=\"active\"and d_id= %s order by id" % d_id
    return querys(sql)


def getSensorsByDeviceID(d_id):
    sql = "select id, d_id, name,value,last,unit,max,min,description,state from sensor where d_id= %s order by id" % d_id
    return querys(sql)


def getHistoryBySensorID(s_id, page_id, page_rows):
    sql = "SELECT id,time,value FROM history WHERE s_id = %s order by id limit %s, %s" % (s_id, str(page_rows * (page_id - 1)), str(page_rows))
    return querys(sql)


def getAlarmBySensorID(s_id, page_id, page_rows):
    sql = "SELECT id,time,value FROM alarm WHERE s_id = %s order by id limit %s, %s" % (s_id, str(page_rows * (page_id - 1)), str(page_rows))
    print(sql)
    return querys(sql)


def getDeviceByID(d_id):
    sql = "select id,name,type,address,serial,state,others,time from device where id=%s order by id" % d_id
    return query(sql)


def getDevicesByUserID(u_id):
    sql = "select device.id,name,type,address,serial,state,others,time from device,user_device where d_id=device.id and u_id=%s" % u_id
    return querys(sql)


def getDevicesByUserIDAtPage(u_id, page_id, page_rows):
    sql = "select device.id,name,type,address,serial,state,others,time from device,user_device where d_id=device.id and u_id=%s order by device.id limit %s, %s  " % (u_id, str(page_rows * (page_id - 1)), str(page_rows))
    return querys(sql)


def getDeviceCountByUserID(u_id):
    sql = "select count(*) from device,user_device where d_id=device.id and u_id=%s" % u_id
    return query(sql)[0]

# 使用 cursor() 方法创建一个游标对象 cursor
# cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
# cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
# data = cursor.fetchone()

# print("Database version : %s " % data)

# 关闭数据库连接
# db.close()
